package com.waikuai.shop.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.waikuai.shop.dto.CardExchangeRequest;
import com.waikuai.shop.dto.CardIssueExcelDTO;
import com.waikuai.shop.dto.WelfareCardQueryDTO;
import com.waikuai.shop.entity.User;
import com.waikuai.shop.entity.WelfareCard;
import com.waikuai.shop.mapper.UserMapper;
import com.waikuai.shop.mapper.WelfareCardMapper;
import com.waikuai.shop.service.WelfareCardService;
import com.waikuai.shop.vo.CardIssueResultVO;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 * 额度卡服务实现类
 * @author waikuai
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class WelfareCardServiceImpl extends ServiceImpl<WelfareCardMapper, WelfareCard>
        implements WelfareCardService {

    private final WelfareCardMapper welfareCardMapper;
    private final UserMapper userMapper;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public com.waikuai.shop.vo.ExchangeResultVO exchangeCard(CardExchangeRequest request, Long userId) {
        try {
            // 验证额度卡
            WelfareCard card = validateCard(request.getCardNumber(), request.getCardPassword());
            if (card == null) {
                log.warn("额度卡验证失败，用户ID: {}, 卡号: {}", userId, maskCardNumber(request.getCardNumber()));
                return null;
            }

            // 获取用户信息
            User user = userMapper.selectById(userId);
            if (user == null) {
                log.error("用户不存在，用户ID: {}", userId);
                return null;
            }

            // 兑换额度卡到余额
            boolean ok = redeemCardToBalance(card, user);
            if (!ok) {
                return null;
            }

            // 查询最新余额
            User latest = userMapper.selectById(userId);
            com.waikuai.shop.vo.ExchangeResultVO vo = new com.waikuai.shop.vo.ExchangeResultVO();
            vo.setAmount(card.getAmount());
            // accountBalance 为 Double，转换为 BigDecimal
            java.math.BigDecimal newBalance = latest.getAccountBalance() == null
                    ? java.math.BigDecimal.ZERO
                    : java.math.BigDecimal.valueOf(latest.getAccountBalance());
            vo.setNewBalance(newBalance);
            return vo;
        } catch (Exception e) {
            log.error("兑换额度卡失败，用户ID: {}, 错误: {}", userId, e.getMessage(), e);
            throw new RuntimeException("兑换额度卡失败");
        }
    }

    @Override
    public List<WelfareCard> getUserCards(Long userId, Integer status) {
        try {
            return welfareCardMapper.selectCardsByUserId(userId, status);
        } catch (Exception e) {
            log.error("获取用户额度卡列表失败，用户ID: {}, 状态: {}", userId, status, e);
            return new ArrayList<>();
        }
    }

    @Override
    public IPage<WelfareCard> getCardPage(WelfareCardQueryDTO queryDTO) {
        try {
            // 构建分页对象
            Page<WelfareCard> page = new Page<>(queryDTO.getCurrent(), queryDTO.getSize());
            
            // 使用自定义的 selectCardPage 方法，支持关联查询用户信息
            return welfareCardMapper.selectCardPage(
                    page,
                    queryDTO.getStatus(),
                    queryDTO.getBatchNo(),
                    queryDTO.getUserId()
            );
        } catch (Exception e) {
            log.error("分页查询额度卡列表失败: {}", e.getMessage(), e);
            throw new RuntimeException("分页查询额度卡列表失败");
        }
    }

    @Override
    public WelfareCard getByCardNumber(String cardNumber) {
        try {
            return welfareCardMapper.selectByCardNumber(cardNumber);
        } catch (Exception e) {
            log.error("根据卡号查询额度卡失败，卡号: {}", maskCardNumber(cardNumber), e);
            return null;
        }
    }

    @Override
    public WelfareCard getByCardNumberAndPassword(String cardNumber, String cardPassword) {
        try {
            return welfareCardMapper.selectByCardNumberAndPassword(cardNumber, cardPassword);
        } catch (Exception e) {
            log.error("根据卡号和卡密查询额度卡失败，卡号: {}", maskCardNumber(cardNumber), e);
            return null;
        }
    }

    @Override
    public BigDecimal sumAvailableAmountByUserId(Long userId) {
        try {
            BigDecimal amount = welfareCardMapper.sumAvailableAmountByUserId(userId);
            return amount != null ? amount : BigDecimal.ZERO;
        } catch (Exception e) {
            log.error("统计用户可用额度卡总额失败，用户ID: {}", userId, e);
            return BigDecimal.ZERO;
        }
    }

    @Override
    public Integer countCardsByStatus(Integer status) {
        try {
            return welfareCardMapper.countCardsByStatus(status);
        } catch (Exception e) {
            log.error("统计各状态额度卡数量失败，状态: {}", status, e);
            return 0;
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean batchGenerateCards(BigDecimal amount, Integer quantity, Integer expireDays, String batchNo) {
        try {
            // 验证面额范围：50~1000000
            if (amount.compareTo(new BigDecimal("50")) < 0 || amount.compareTo(new BigDecimal("1000000")) > 0) {
                log.error("面额必须在50-1000000之间，当前面额: {}", amount);
                throw new RuntimeException("面额必须在50-1000000之间");
            }

            // 验证数量
            if (quantity <= 0 || quantity > 10000) {
                log.error("生成数量必须在1-10000之间，当前数量: {}", quantity);
                throw new RuntimeException("生成数量必须在1-10000之间");
            }

            List<WelfareCard> cards = new ArrayList<>();

            // 生成批次号
            if (batchNo == null || batchNo.trim().isEmpty()) {
                batchNo = "BATCH_" + System.currentTimeMillis();
            }

            // 批量生成时先不设置过期时间（发放时再设置）
            for (int i = 0; i < quantity; i++) {
                WelfareCard card = new WelfareCard();
                card.setCardNumber(generateCardNumber());
                card.setCardPassword(generateCardPassword());
                card.setAmount(amount);
                card.setStatus(0); // 未发放
                card.setBatchNo(batchNo);
                // expire_time 在发放时设置
                cards.add(card);
            }

            // 批量插入
            boolean result = saveBatch(cards);
            if (result) {
                log.info("批量生成额度卡成功，面额: {}, 数量: {}, 批次号: {}", amount, quantity, batchNo);
            }
            return result;
        } catch (Exception e) {
            log.error("批量生成额度卡失败: {}", e.getMessage(), e);
            throw new RuntimeException("批量生成额度卡失败: " + e.getMessage());
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean batchIssueCards(List<Long> cardIds, Long userId) {
        try {
            LocalDateTime now = LocalDateTime.now();
            // 设置过期时间为1年后
            LocalDateTime expireTime = now.plusYears(1);

            LambdaUpdateWrapper<WelfareCard> updateWrapper = new LambdaUpdateWrapper<>();
            updateWrapper.in(WelfareCard::getId, cardIds)
                         .eq(WelfareCard::getStatus, 0) // 只发放未发放的卡
                         .set(WelfareCard::getStatus, 1) // 已发放
                         .set(WelfareCard::getUserId, userId)
                         .set(WelfareCard::getIssueTime, now)
                         .set(WelfareCard::getExpireTime, expireTime);

            int result = welfareCardMapper.update(null, updateWrapper);
            if (result > 0) {
                log.info("批量发放额度卡成功，数量: {}, 用户ID: {}, 过期时间: {}", result, userId, expireTime);
            } else {
                log.warn("批量发放额度卡失败，可能部分卡已被发放，数量: {}", cardIds.size());
            }
            return result > 0;
        } catch (Exception e) {
            log.error("批量发放额度卡失败: {}", e.getMessage(), e);
            throw new RuntimeException("批量发放额度卡失败");
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean updateCardStatusBatch(List<Long> cardIds, Integer status) {
        try {
            LambdaUpdateWrapper<WelfareCard> updateWrapper = new LambdaUpdateWrapper<>();
            updateWrapper.in(WelfareCard::getId, cardIds)
                         .set(WelfareCard::getStatus, status);

            int result = welfareCardMapper.update(null, updateWrapper);
            if (result > 0) {
                log.info("批量更新额度卡状态成功，数量: {}, 新状态: {}", result, status);
            }
            return result > 0;
        } catch (Exception e) {
            log.error("批量更新额度卡状态失败: {}", e.getMessage(), e);
            throw new RuntimeException("批量更新额度卡状态失败");
        }
    }

    @Override
    public boolean checkCardAvailable(String cardNumber, String cardPassword) {
        try {
            WelfareCard card = validateCard(cardNumber, cardPassword);
            return card != null;
        } catch (Exception e) {
            log.error("检查额度卡可用性失败: {}", e.getMessage(), e);
            return false;
        }
    }

    @Override
    public WelfareCard validateCard(String cardNumber, String cardPassword) {
        try {
            WelfareCard card = welfareCardMapper.selectByCardNumberAndPassword(cardNumber, cardPassword);
            if (card == null) {
                log.warn("额度卡不存在或密码错误，卡号: {}", maskCardNumber(cardNumber));
                return null;
            }

            // 检查状态
            if (card.getStatus() != 1) {
                log.warn("额度卡状态不正确，卡号: {}, 状态: {}", maskCardNumber(cardNumber), card.getStatus());
                return null;
            }

            // 检查是否过期
            if (card.getExpireTime().isBefore(LocalDateTime.now())) {
                log.warn("额度卡已过期，卡号: {}, 过期时间: {}", maskCardNumber(cardNumber), card.getExpireTime());
                return null;
            }

            return card;
        } catch (Exception e) {
            log.error("验证额度卡失败: {}", e.getMessage(), e);
            return null;
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean redeemCardToBalance(WelfareCard card, User user) {
        try {
            // 更新额度卡状态
            card.setStatus(2); // 已兑换
            card.setRedeemTime(LocalDateTime.now());
            welfareCardMapper.updateById(card);

            // 更新用户余额
            LambdaUpdateWrapper<User> userUpdateWrapper = new LambdaUpdateWrapper<>();
            userUpdateWrapper.eq(User::getId, user.getId())
                           .setSql("account_balance = account_balance + " + card.getAmount());
            userMapper.update(null, userUpdateWrapper);

            log.info("额度卡兑换成功，卡号: {}, 用户ID: {}, 兑换金额: {}",
                    maskCardNumber(card.getCardNumber()), user.getId(), card.getAmount());
            return true;
        } catch (Exception e) {
            log.error("兑换额度卡到余额失败: {}", e.getMessage(), e);
            throw new RuntimeException("兑换额度卡失败");
        }
    }

    /**
     * 生成16位卡号（大写字母+数字随机组合）
     */
    private String generateCardNumber() {
        return generateRandomString(16);
    }

    /**
     * 生成16位卡密码（大写字母+数字随机组合）
     */
    private String generateCardPassword() {
        return generateRandomString(16);
    }

    /**
     * 生成指定长度的随机字符串（大写字母+数字）
     * @param length 字符串长度
     * @return 随机字符串
     */
    private String generateRandomString(int length) {
        String chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
        StringBuilder sb = new StringBuilder();
        java.security.SecureRandom random = new java.security.SecureRandom();
        
        for (int i = 0; i < length; i++) {
            int index = random.nextInt(chars.length());
            sb.append(chars.charAt(index));
        }
        
        return sb.toString();
    }

    /**
     * 隐藏卡号敏感信息
     */
    private String maskCardNumber(String cardNumber) {
        if (cardNumber == null || cardNumber.length() <= 8) {
            return "****";
        }
        return cardNumber.substring(0, 4) + "****" + cardNumber.substring(cardNumber.length() - 4);
    }

    @Override
    public List<WelfareCard> getCardsByBatchNo(String batchNo) {
        try {
            return welfareCardMapper.selectCardsByBatchNo(batchNo);
        } catch (Exception e) {
            log.error("根据批次号查询额度卡失败，批次号: {}", batchNo, e);
            return new ArrayList<>();
        }
    }

    @Override
    public List<WelfareCard> getCardsExpiringSoon(Integer days) {
        try {
            return welfareCardMapper.selectCardsExpiringSoon(days);
        } catch (Exception e) {
            log.error("获取即将过期的额度卡列表失败: {}", e.getMessage(), e);
            return new ArrayList<>();
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public CardIssueResultVO importAndIssueCards(MultipartFile file) {
        CardIssueResultVO result = new CardIssueResultVO();
        List<CardIssueResultVO.CardIssueDetail> successList = new ArrayList<>();
        List<CardIssueResultVO.CardIssueFailDetail> failList = new ArrayList<>();
        
        try {
            // 解析Excel文件
            List<CardIssueExcelDTO> excelData = parseExcelFile(file);
            result.setTotal(excelData.size());
            
            for (CardIssueExcelDTO dto : excelData) {
                try {
                    // 查找用户（优先按工号，再按姓名）
                    User user = findUserByEmployeeInfo(dto.getEmployeeNo(), dto.getEmployeeName());
                    
                    if (user == null) {
                        CardIssueResultVO.CardIssueFailDetail failDetail = new CardIssueResultVO.CardIssueFailDetail();
                        failDetail.setEmployeeNo(dto.getEmployeeNo());
                        failDetail.setEmployeeName(dto.getEmployeeName());
                        failDetail.setReason("员工不存在");
                        failList.add(failDetail);
                        continue;
                    }
                    
                    // 验证面额
                    BigDecimal amount = BigDecimal.valueOf(dto.getCardAmount());
                    if (amount.compareTo(new BigDecimal("50")) < 0 || amount.compareTo(new BigDecimal("1000000")) > 0) {
                        CardIssueResultVO.CardIssueFailDetail failDetail = new CardIssueResultVO.CardIssueFailDetail();
                        failDetail.setEmployeeNo(dto.getEmployeeNo());
                        failDetail.setEmployeeName(dto.getEmployeeName());
                        failDetail.setReason("面额必须在50-1000000之间");
                        failList.add(failDetail);
                        continue;
                    }
                    
                    // 生成并发放卡
                    WelfareCard card = generateAndIssueCard(user.getId(), amount);
                    
                    // 添加到成功列表
                    CardIssueResultVO.CardIssueDetail detail = new CardIssueResultVO.CardIssueDetail();
                    detail.setEmployeeNo(user.getEmployeeNo());
                    detail.setEmployeeName(user.getRealName());
                    detail.setCardNumber(card.getCardNumber());
                    detail.setCardPassword(card.getCardPassword());
                    detail.setAmount(amount.doubleValue());
                    successList.add(detail);
                    
                } catch (Exception e) {
                    log.error("处理员工发放失败，员工: {}, 错误: {}", 
                        dto.getEmployeeName(), e.getMessage(), e);
                    CardIssueResultVO.CardIssueFailDetail failDetail = new CardIssueResultVO.CardIssueFailDetail();
                    failDetail.setEmployeeNo(dto.getEmployeeNo());
                    failDetail.setEmployeeName(dto.getEmployeeName());
                    failDetail.setReason("发放失败: " + e.getMessage());
                    failList.add(failDetail);
                }
            }
            
            result.setSuccessCount(successList.size());
            result.setFailCount(failList.size());
            result.setSuccessList(successList);
            result.setFailList(failList);
            
            log.info("Excel导入发放完成，总数: {}, 成功: {}, 失败: {}", 
                result.getTotal(), result.getSuccessCount(), result.getFailCount());
            
        } catch (Exception e) {
            log.error("Excel导入发放失败: {}", e.getMessage(), e);
            throw new RuntimeException("Excel导入失败: " + e.getMessage());
        }
        
        return result;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public CardIssueResultVO batchIssueToUsers(List<Long> userIds, BigDecimal amount, Integer countPerUser) {
        CardIssueResultVO result = new CardIssueResultVO();
        List<CardIssueResultVO.CardIssueDetail> successList = new ArrayList<>();
        List<CardIssueResultVO.CardIssueFailDetail> failList = new ArrayList<>();
        
        try {
            if (countPerUser == null || countPerUser < 1) {
                countPerUser = 1;
            }
            
            result.setTotal(userIds.size());
            int successCount = 0;
            int failCount = 0;
            
            for (Long userId : userIds) {
                try {
                    User user = userMapper.selectById(userId);
                    if (user == null) {
                        failCount++;
                        CardIssueResultVO.CardIssueFailDetail failDetail = new CardIssueResultVO.CardIssueFailDetail();
                        failDetail.setReason("用户不存在");
                        failList.add(failDetail);
                        continue;
                    }
                    
                    // 为每个员工生成指定张数的卡
                    for (int i = 0; i < countPerUser; i++) {
                        WelfareCard card = generateAndIssueCard(userId, amount);
                        
                        CardIssueResultVO.CardIssueDetail detail = new CardIssueResultVO.CardIssueDetail();
                        detail.setEmployeeNo(user.getEmployeeNo());
                        detail.setEmployeeName(user.getRealName());
                        detail.setCardNumber(card.getCardNumber());
                        detail.setCardPassword(card.getCardPassword());
                        detail.setAmount(amount.doubleValue());
                        successList.add(detail);
                        successCount++;
                    }
                    
                } catch (Exception e) {
                    log.error("发放失败，用户ID: {}, 错误: {}", userId, e.getMessage(), e);
                    failCount++;
                    CardIssueResultVO.CardIssueFailDetail failDetail = new CardIssueResultVO.CardIssueFailDetail();
                    failDetail.setReason("发放失败: " + e.getMessage());
                    failList.add(failDetail);
                }
            }
            
            result.setSuccessCount(successCount);
            result.setFailCount(failCount);
            result.setSuccessList(successList);
            result.setFailList(failList);
            
            log.info("批量发放完成，总数: {}, 成功: {}, 失败: {}", 
                result.getTotal(), result.getSuccessCount(), result.getFailCount());
            
        } catch (Exception e) {
            log.error("批量发放失败: {}", e.getMessage(), e);
            throw new RuntimeException("批量发放失败: " + e.getMessage());
        }
        
        return result;
    }

    /**
     * 解析Excel文件
     */
    private List<CardIssueExcelDTO> parseExcelFile(MultipartFile file) throws Exception {
        List<CardIssueExcelDTO> dataList = new ArrayList<>();
        
        String fileName = file.getOriginalFilename();
        if (fileName == null || (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx"))) {
            throw new RuntimeException("文件格式不正确，只支持.xls和.xlsx格式");
        }
        
        Workbook workbook = null;
        if (fileName.endsWith(".xls")) {
            workbook = new HSSFWorkbook(file.getInputStream());
        } else {
            workbook = new XSSFWorkbook(file.getInputStream());
        }
        
        Sheet sheet = workbook.getSheetAt(0);
        int rowCount = sheet.getLastRowNum();
        
        // 从第2行开始读取数据（第1行是表头）
        for (int i = 1; i <= rowCount; i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;
            
            CardIssueExcelDTO dto = new CardIssueExcelDTO();
            
            // 读取员工工号（第1列）
            Cell cell0 = row.getCell(0);
            if (cell0 != null) {
                dto.setEmployeeNo(getCellValue(cell0));
            }
            
            // 读取员工姓名（第2列）
            Cell cell1 = row.getCell(1);
            if (cell1 != null) {
                dto.setEmployeeName(getCellValue(cell1));
            }
            
            // 读取卡面额（第3列）
            Cell cell2 = row.getCell(2);
            if (cell2 != null) {
                String value = getCellValue(cell2);
                if (value != null && !value.isEmpty()) {
                    try {
                        dto.setCardAmount(Double.parseDouble(value));
                    } catch (Exception e) {
                        log.warn("面额解析失败: {}", value);
                    }
                }
            }
            
            // 跳过空行
            if (dto.getEmployeeNo() == null && dto.getEmployeeName() == null) {
                continue;
            }
            
            dataList.add(dto);
        }
        
        workbook.close();
        return dataList;
    }

    /**
     * 获取单元格值
     */
    private String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    /**
     * 根据员工工号或姓名查找用户
     */
    private User findUserByEmployeeInfo(String employeeNo, String employeeName) {
        LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
        
        // 优先按工号查找
        if (employeeNo != null && !employeeNo.trim().isEmpty()) {
            wrapper.eq(User::getEmployeeNo, employeeNo);
            List<User> users = userMapper.selectList(wrapper);
            if (!users.isEmpty()) {
                return users.get(0);
            }
        }
        
        // 再按姓名查找
        if (employeeName != null && !employeeName.trim().isEmpty()) {
            wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(User::getRealName, employeeName);
            List<User> users = userMapper.selectList(wrapper);
            if (!users.isEmpty()) {
                return users.get(0);
            }
        }
        
        return null;
    }

    /**
     * 生成并发放卡
     */
    private WelfareCard generateAndIssueCard(Long userId, BigDecimal amount) {
        LocalDateTime now = LocalDateTime.now();
        LocalDateTime expireTime = now.plusYears(1);
        
        WelfareCard card = new WelfareCard();
        card.setCardNumber(generateCardNumber());
        card.setCardPassword(generateCardPassword());
        card.setAmount(amount);
        card.setStatus(1); // 已发放
        card.setUserId(userId);
        card.setBatchNo("IMPORT_" + System.currentTimeMillis());
        card.setIssueTime(now);
        card.setExpireTime(expireTime);
        
        welfareCardMapper.insert(card);
        
        return card;
    }

    @Override
    public List<WelfareCard> getUnissuedCardsByBatch(String batchNo) {
        try {
            LambdaQueryWrapper<WelfareCard> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(WelfareCard::getBatchNo, batchNo)
                   .eq(WelfareCard::getStatus, 0); // 只查询未发放的
            return welfareCardMapper.selectList(wrapper);
        } catch (Exception e) {
            log.error("查询未发放额度卡失败，批次号: {}", batchNo, e);
            return new ArrayList<>();
        }
    }

    @Override
    public List<WelfareCard> getMyCardsWithCredentials(Long userId, Integer status) {
        try {
            LambdaQueryWrapper<WelfareCard> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(WelfareCard::getUserId, userId);
            if (status != null) {
                wrapper.eq(WelfareCard::getStatus, status);
            }
            return welfareCardMapper.selectList(wrapper);
        } catch (Exception e) {
            log.error("获取员工额度卡详情失败，用户ID: {}", userId, e);
            return new ArrayList<>();
        }
    }

    @Override
    public void downloadTemplate(javax.servlet.http.HttpServletResponse response) {
        log.info("开始下载Excel模板");
        
        try {
            // 创建工作簿
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("额度卡发放");
            
            // 创建单元格样式
            CellStyle headerStyle = workbook.createCellStyle();
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerFont.setFontHeightInPoints((short) 12);
            headerFont.setColor(IndexedColors.WHITE.getIndex());
            headerStyle.setFont(headerFont);
            headerStyle.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headerStyle.setAlignment(HorizontalAlignment.CENTER);
            headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            headerStyle.setBorderBottom(BorderStyle.THIN);
            headerStyle.setBorderTop(BorderStyle.THIN);
            headerStyle.setBorderLeft(BorderStyle.THIN);
            headerStyle.setBorderRight(BorderStyle.THIN);
            
            CellStyle dataStyle = workbook.createCellStyle();
            dataStyle.setBorderBottom(BorderStyle.THIN);
            dataStyle.setBorderTop(BorderStyle.THIN);
            dataStyle.setBorderLeft(BorderStyle.THIN);
            dataStyle.setBorderRight(BorderStyle.THIN);
            dataStyle.setAlignment(HorizontalAlignment.LEFT);
            dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            
            // 创建表头
            Row headerRow = sheet.createRow(0);
            headerRow.setHeightInPoints(20);
            
            String[] headers = {"员工工号", "员工姓名", "卡面额"};
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
            }
            
            // 设置列宽
            sheet.setColumnWidth(0, 4000);
            sheet.setColumnWidth(1, 4000);
            sheet.setColumnWidth(2, 4000);
            
            // 添加示例数据
            Row exampleRow = sheet.createRow(1);
            Cell cell0 = exampleRow.createCell(0);
            cell0.setCellValue("E001");
            cell0.setCellStyle(dataStyle);
            
            Cell cell1 = exampleRow.createCell(1);
            cell1.setCellValue("张三");
            cell1.setCellStyle(dataStyle);
            
            Cell cell2 = exampleRow.createCell(2);
            cell2.setCellValue(100.0);
            cell2.setCellStyle(dataStyle);
            
            // 设置响应头
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = "额度卡发放模板_" + System.currentTimeMillis() + ".xlsx";
            response.setHeader("Content-disposition", 
                "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
            
            // 输出文件
            try (java.io.OutputStream outputStream = response.getOutputStream()) {
                workbook.write(outputStream);
                outputStream.flush();
            } finally {
                workbook.close();
            }
            
            log.info("Excel模板下载成功");
            
        } catch (java.io.IOException e) {
            log.error("下载Excel模板失败: {}", e.getMessage(), e);
            try {
                if (!response.isCommitted()) {
                    response.reset();
                    response.setStatus(javax.servlet.http.HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                    response.setContentType("application/json;charset=UTF-8");
                    response.getWriter().write("{\"code\":500,\"message\":\"下载模板失败\"}");
                }
            } catch (Exception ignored) {
            }
        } catch (Exception e) {
            log.error("下载Excel模板失败: {}", e.getMessage(), e);
            try {
                if (!response.isCommitted()) {
                    response.reset();
                    response.setStatus(javax.servlet.http.HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                    response.setContentType("application/json;charset=UTF-8");
                    response.getWriter().write("{\"code\":500,\"message\":\"下载模板失败\"}");
                }
            } catch (Exception ignored) {
            }
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public com.waikuai.shop.vo.AutoRedeemResultVO autoRedeemIssuedCards(Long userId) {
        com.waikuai.shop.vo.AutoRedeemResultVO result = new com.waikuai.shop.vo.AutoRedeemResultVO();
        
        try {
            log.info("开始自动兑换用户已发放的额度卡，用户ID: {}", userId);
            
            // 查询用户所有已发放(status=1)且未过期的额度卡
            LambdaQueryWrapper<WelfareCard> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(WelfareCard::getUserId, userId)
                   .eq(WelfareCard::getStatus, 1) // 已发放状态
                   .gt(WelfareCard::getExpireTime, LocalDateTime.now()); // 未过期
            
            List<WelfareCard> issuedCards = welfareCardMapper.selectList(wrapper);
            
            if (issuedCards == null || issuedCards.isEmpty()) {
                log.info("用户没有可兑换的额度卡，用户ID: {}", userId);
                return result;
            }
            
            log.info("找到{}张待兑换的额度卡，用户ID: {}", issuedCards.size(), userId);
            
            // 获取用户信息
            User user = userMapper.selectById(userId);
            if (user == null) {
                log.error("用户不存在，自动兑换失败，用户ID: {}", userId);
                return result;
            }
            
            // 循环兑换每张卡
            int successCount = 0;
            BigDecimal totalAmount = BigDecimal.ZERO;
            
            for (WelfareCard card : issuedCards) {
                try {
                    // 再次验证卡片状态（防止并发问题）
                    WelfareCard currentCard = welfareCardMapper.selectById(card.getId());
                    if (currentCard == null || currentCard.getStatus() != 1) {
                        log.warn("卡片状态已变更，跳过兑换，卡号: {}", maskCardNumber(card.getCardNumber()));
                        continue;
                    }
                    
                    // 兑换到余额
                    boolean success = redeemCardToBalance(currentCard, user);
                    if (success) {
                        successCount++;
                        totalAmount = totalAmount.add(currentCard.getAmount());
                        log.info("自动兑换成功，卡号: {}, 金额: {}", maskCardNumber(card.getCardNumber()), card.getAmount());
                    } else {
                        result.getFailedCardNumbers().add(maskCardNumber(card.getCardNumber()));
                        log.warn("自动兑换失败，卡号: {}", maskCardNumber(card.getCardNumber()));
                    }
                } catch (Exception e) {
                    result.getFailedCardNumbers().add(maskCardNumber(card.getCardNumber()));
                    log.error("自动兑换额度卡异常，卡号: {}, 错误: {}", maskCardNumber(card.getCardNumber()), e.getMessage(), e);
                }
            }
            
            result.setSuccessCount(successCount);
            result.setTotalAmount(totalAmount);
            
            log.info("自动兑换完成，用户ID: {}, 成功: {}, 总金额: {}, 失败: {}", 
                    userId, successCount, totalAmount, result.getFailedCardNumbers().size());
            
            return result;
            
        } catch (Exception e) {
            log.error("自动兑换额度卡失败，用户ID: {}, 错误: {}", userId, e.getMessage(), e);
            return result;
        }
    }
}